﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
     public sealed class PurchaseRequsteRouteDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void Insert(IList<PurchaseRequestRouteInfo> ListInfo, int PurchaseRequestSeq, int UserId, IDBManager dbManagerTrans)
        {
            string strSQL;
            DataSet ds;
            Nullable<int> lastSeq = null;
            try
            {
                IList<PurchaseRequestRouteInfo> OldListInfo = new List<PurchaseRequestRouteInfo>();
                OldListInfo = GetPurchaseRequestRoute(PurchaseRequestSeq, dbManagerTrans);
                CopyToHistory(OldListInfo, UserId, dbManagerTrans);

                dbManagerTrans.CreateParameters(11);

                for (int i = 0; i < ListInfo.Count; ++i)
                {
                    dbManagerTrans.AddParameters(0, "@icm_pr_seq", ListInfo[i].InfoPurchaseRequest.Seq);
                    dbManagerTrans.AddParameters(1, "@from_emp_seq", ListInfo[i].InfoFromEmp.Seq);
                    dbManagerTrans.AddParameters(2, "@to_emp_seq", ListInfo[i].InfoToEmp.Seq);
                    dbManagerTrans.AddParameters(3, "@remark", ListInfo[i].Remark);
                    dbManagerTrans.AddParameters(4, "@order_seq", ListInfo[i].Order);
                    dbManagerTrans.AddParameters(5, "@upper_icm_pr_route_seq", (ListInfo[i].Type == "APPROVE") ? lastSeq : null);
                    dbManagerTrans.AddParameters(6, "@route_type", ListInfo[i].Type);
                    dbManagerTrans.AddParameters(7, "@active", ListInfo[i].Active);
                    dbManagerTrans.AddParameters(8, "@route_status", ListInfo[i].InfoRouteStatus.Status);
                    dbManagerTrans.AddParameters(9, "@created_by", ListInfo[i].InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(10, "@created_when", ListInfo[i].InfoRecord.Created_when);

                    strSQL = "insert into icm_purchase_request_route (" +
                                "icm_pr_seq," +
                                "from_emp_seq," +
                                "to_emp_seq," +
                                "remark," +
                                "order_seq," +
                                "upper_icm_pr_route_seq," +
                                "route_type," +
                                "route_status," +
                                "created_by," +
                                "created_when)" +
                            " values(" +
                                "@icm_pr_seq," +
                                "@from_emp_seq," +
                                "@to_emp_seq," +
                                "@remark," +
                                "@order_seq," +
                                "@upper_icm_pr_route_seq," +
                                "@route_type," +
                                "@route_status," +
                                "@created_by," +
                                "@created_when)";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                    strSQL = "SELECT LAST_INSERT_ID()"
                        + " FROM icm_purchase_request_route";
                    ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IList<PurchaseRequestRouteInfo> GetPurchaseRequestRoute(int PurchaseRequestSeq, IDBManager dbManagerTrans)
        {
            string strSQL;
            DataSet ds;
            DataRowCollection dr;
            IList<PurchaseRequestRouteInfo> InfoList = new List<PurchaseRequestRouteInfo>();
            try
            {
                dbManagerTrans.CreateParameters(1);

                dbManagerTrans.AddParameters(0, "@icm_pr_seq", PurchaseRequestSeq);

                strSQL = "SELECT r.icm_pr_route_seq," +
                            "r.icm_pr_seq," +
                            "r.from_emp_seq," +
                            "r.to_emp_seq," +
                            "r.remark," +
                            "r.order_seq, " +
                            "r.upper_icm_pr_route_seq," +
                            "r.route_type," +
                            "rs.route_status_name," +
                            "r.route_status" +
                        " FROM icm_purchase_request_route r" +
                            " inner join sys_route_status rs on r.route_status = rs.route_status" +
                        " WHERE icm_pr_seq = @icm_pr_seq";

                ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseRequestRouteInfo info = new PurchaseRequestRouteInfo();
                        PurchaseRequestInfo InfoPpRequest = new PurchaseRequestInfo();
                        HrmEmployeeInfo InfoFromEmp = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoToEmp = new HrmEmployeeInfo();
                        SysRouteStatusInfo InfoStatusRoute = new SysRouteStatusInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_pr_route_seq"].ToString());

                        InfoPpRequest.Seq = Convert.ToInt32(dr[i]["icm_pr_seq"].ToString());
                        info.InfoPurchaseRequest = InfoPpRequest;

                        InfoFromEmp.Seq = Convert.ToInt32(dr[i]["from_emp_seq"].ToString());
                        //InfoFromEmp.Name = (dr[i]["from_emp"]).ToString();
                        info.InfoFromEmp = InfoFromEmp;

                        infoToEmp.Seq = Convert.ToInt32(dr[i]["to_emp_seq"].ToString());
                        //infoToEmp.Name = (dr[i]["to_emp"]).ToString();
                        info.InfoToEmp = infoToEmp;

                        info.Type = (dr[i]["route_type"]).ToString();

                        info.Remark = (dr[i]["remark"]).ToString();

                        info.Order = (Convert.IsDBNull(dr[i]["order_seq"]) ? null : (Nullable<int>)(dr[i]["order_seq"]));

                        info.UpperSeq = (Convert.IsDBNull(dr[i]["upper_icm_pr_route_seq"]) ? null : (Nullable<int>)(dr[i]["upper_icm_pr_route_seq"]));

                        InfoStatusRoute.Name = (dr[i]["route_status_name"]).ToString();
                        info.InfoRouteStatus = InfoStatusRoute;

                        info.Status = (dr[i]["route_status"]).ToString();

                        InfoList.Add(info);
                    }
                }
                return InfoList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void CopyToHistory(IList<PurchaseRequestRouteInfo> ListInfo, int UserId, IDBManager dbManagerTrans)
        {
            try
            {
                PurchaseRequestRouteHistoryDAL dalPprRouteHis = new PurchaseRequestRouteHistoryDAL();
                if (ListInfo.Count == 0)
                    return;
                int TmpPprSeq = (ListInfo[0].InfoPurchaseRequest.Seq == null) ? 0 : (int)ListInfo[0].InfoPurchaseRequest.Seq;
                int TmpRevisionNo = dalPprRouteHis.GetNextRevisionNo(dbManagerTrans, TmpPprSeq);
                IList<PurchaseRequestRouteHistoryInfo> ListPrRouteHistory = new List<PurchaseRequestRouteHistoryInfo>();
                foreach (PurchaseRequestRouteInfo current in ListInfo)
                {
                    PurchaseRequestRouteHistoryInfo PrRouteHistory = new PurchaseRequestRouteHistoryInfo();
                    PrRouteHistory.RevisionNo = TmpRevisionNo;
                    PrRouteHistory.InfoFromEmp = current.InfoFromEmp;
                    PrRouteHistory.InfoToEmp = current.InfoToEmp;
                    PrRouteHistory.Remark = current.Remark;
                    PrRouteHistory.Order = current.Order;
                    PrRouteHistory.UpperSeq = current.UpperSeq;
                    PrRouteHistory.Status = current.Status;
                    PrRouteHistory.Type = current.Type;
                    RecordInfo InfoRecord = new RecordInfo();
                    InfoRecord.Created_by = UserId;
                    InfoRecord.Created_when = DateTime.Now;
                    PrRouteHistory.InfoRecord = InfoRecord;
                    PrRouteHistory.InfoPurchaseRequest = current.InfoPurchaseRequest;
                    ListPrRouteHistory.Add(PrRouteHistory);
                }
                dalPprRouteHis.Insert(ListPrRouteHistory, dbManagerTrans);
                DeleteAll(ListInfo, dbManagerTrans);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteAll(IList<PurchaseRequestRouteInfo> infoList, IDBManager dbManagerTrans)
        {
            try
            {
                string strSQL;
                string Seq = "";
                for (int i = 0; i < infoList.Count; ++i)
                {
                    Seq += infoList[i].Seq + ",";
                }
                Seq = Seq.Substring(0, Seq.Length - 1);

                strSQL = "delete from icm_purchase_request_route where icm_pr_route_seq in (" + Seq + ")";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
